8  Health System Data Tables

8.1 Table name: LOCATION

8.1.1 Reading from environment variable (SHAK_code) and SHAK code lookup file

Note: to avoid adding in locations that will not be used by the ETL, we will ask the user via an environment variable what the SHAK code associated with the dataset is.

Destination Field	Source Field	Logic	Comment
location_id	Hospital_SHAK_code (SHAK code look up file)	Autogenerate integer	Use the SHAK_code environment variable to do the lookup in the SHAK code lookup file against the SHAK_code field. Only create one distinct record for the returned entry.
address_1			NULL
address_2			NULL
city			NULL
state			NULL
zip	postal_code (SHAK code look up file)	Use postal code (4 digits)
county			NULL
location_source_value	Hospital_SHAK_code (environment variable)	<column_name>|<column_value>
country_concept_id		4330435 [Denmark]
conntry_source_value			NULL
latitude			NULL
longitude			NULL

8.2 Table name: CARE_SITE

8.2.1 Reading from environment variable (SHAK_code) and SHAK code lookup file

Note: to avoid adding in locations that will not be used by the ETL, we will ask the user via an environment variable what the SHAK code associated with the dataset is.

Destination Field	Source Field	Logic	Comment
care_site_id	Autogenerate integer	Use the SHAK_code environment variable to do the lookup in the SHAK code lookup file against the SHAK_code field. Only create a record for the returned entry.
care_site_name	department_name (SHAK code look up file)	use the department name from the SHAK lookup file
place_of_service_conce	pt_id department_type (SHAK code look up file)	First join the SHAK_codes (env variable and the SHAK code look up file) then look up the department_type in the concept_look up file by joining on the source_code field and the department_type and filtering on ‘care_site’, use the corresponding standard_concept_id (lookup based on SHAK department code) E.g. 32037 [Intensive Care]
location_id	SHAK_code (environment variable)	Join in the location table using the location_source_value and the SHAK_code (<column_name>|<column_value> format) to find the corresponding location_id
care_site_source_value	SHAK_code (environment variable)	<column_name>|<column_value>	SHAK code encodes hospital + department
place_of_service_source_value	department_type (SHAK code look up file)	<column_name>|<column_value>